﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using StockFinder.Model;

namespace StockFinder.DataAccess
{
    public class DailyPriceDataAccess : DataAccessBase
    {
        private const string INSERT_DAILYPRICE_COMMAND = "dbo.InsertDailyPrice";
        private const string DOES_DAILYPRICE_EXIST_BY_NAME_COMMAND = "dbo.DoesDailyPriceExist";
        private const string GET_ALL_DAILY_PRICES_BY_SYMBOLNAME_FROM_DATE = "dbo.GetAllDailyPricesBySymbolNameFromDate";
        private const string GET_ALL_DAILY_PRICES_BY_SYMBOLID_FROM_DATE = "dbo.GetAllDailyPricesBySymbolIdFromDate";
        private const string GET_ALL_DAILY_PRICES_BY_SYMBOLID = "dbo.GetAllDailyPricesBySymbolId";
        private const string GET_DAILY_PRICES_BY_SYMBOL = "dbo.GetDailyPricesBySymbol";
        private const string TRUNCATE_DAILY_PRICE_TABLE = "dbo.[TruncateDailyPriceTable]";
        private const string CREATE_SYMBOL_PRICE_INDEX = "dbo.CreateSymbolPriceIndex";
        private const string REMOVE_SYMBOL_PRICE_INDEX = "dbo.[RemoveSymbolPriceIndex]";
        private const string REBUILD_SYMBOL_PRICE_INDEX = "dbo.[RebuildSymbolPriceIndex]";
        private const string DISABLE_SYMBOL_PRICE_INDEX = "dbo.DisableSymbolPriceIndex";

        public static void DisablePriceIndex()
        {
            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(DISABLE_SYMBOL_PRICE_INDEX, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    command.CommandTimeout = 600; //10 mins
                    command.ExecuteNonQuery();
                }
            }
        }

        public static void RebuildPriceIndex()
        {
            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(REBUILD_SYMBOL_PRICE_INDEX, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    command.CommandTimeout = 600; //10 mins
                    command.ExecuteNonQuery();
                }
            }
        }

        public static void RemoveDailyPriceIndex()
        {
            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(REMOVE_SYMBOL_PRICE_INDEX, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    command.CommandTimeout = 600; //10 mins
                    command.ExecuteNonQuery();
                }
            }
        }

        public static void CreateDailyPriceIndex()
        {
            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(CREATE_SYMBOL_PRICE_INDEX, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    command.CommandTimeout = 600; //10 mins
                    command.ExecuteNonQuery();
                }
            }
        }

        public static void TruncateDailyPriceTable()
        {
            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(TRUNCATE_DAILY_PRICE_TABLE, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        public static void TruncatePriceDataTable()
        {
            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand("truncate table dbo.PriceData", connection) { CommandType = System.Data.CommandType.Text })
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        public static void TruncatePriceDataPerformanceTable()
        {
            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand("truncate table dbo.[PriceDataPerformance]", connection) { CommandType = System.Data.CommandType.Text })
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        public static void InsertDailyPrices(List<DailyPrice> prices)
        {
            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(INSERT_DAILYPRICE_COMMAND, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    command.Parameters.Add("@Symbol", System.Data.SqlDbType.VarChar);
                    command.Parameters.Add("@Open", System.Data.SqlDbType.Decimal);
                    command.Parameters.Add("@High", System.Data.SqlDbType.Decimal);
                    command.Parameters.Add("@Low", System.Data.SqlDbType.Decimal);
                    command.Parameters.Add("@Close", System.Data.SqlDbType.Decimal);
                    command.Parameters.Add("@Volume", System.Data.SqlDbType.BigInt);
                    command.Parameters.Add("@PriceDate", System.Data.SqlDbType.DateTime);
                    command.Parameters.Add("@AdjustedClose", System.Data.SqlDbType.Decimal);
                    command.Parameters.Add("@AdjustedHigh", System.Data.SqlDbType.Decimal);
                    command.Parameters.Add("@AdjustedOpen", System.Data.SqlDbType.Decimal);
                    command.Parameters.Add("@AdjustedLow", System.Data.SqlDbType.Decimal);

                    //insert prices
                    foreach (var price in prices)
                    {
                        command.Parameters["@Symbol"].Value = price.Symbol;
                        command.Parameters["@Open"].Value = price.Open;
                        command.Parameters["@High"].Value = price.High;
                        command.Parameters["@Low"].Value = price.Low;
                        command.Parameters["@Close"].Value = price.Close;
                        command.Parameters["@Volume"].Value = price.Volume;
                        command.Parameters["@PriceDate"].Value = price.PriceDate;
                        command.Parameters["@AdjustedClose"].Value = price.AdjustedClose;
                        command.Parameters["@AdjustedHigh"].Value = price.AdjustedHigh;
                        command.Parameters["@AdjustedOpen"].Value = price.AdjustedOpen;
                        command.Parameters["@AdjustedLow"].Value = price.AdjustedLow;

                        command.ExecuteNonQuery();
                    }
                }
            }
        }        

        public void InsertDailyPrice(DailyPrice dailyPrice)
        {
            SqlConnection connection = CreateOpenConnection();
            
            using (SqlCommand command = new SqlCommand(INSERT_DAILYPRICE_COMMAND, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("@SymbolId", dailyPrice.SymbolId);
                command.Parameters.AddWithValue("@Open", dailyPrice.Open);
                command.Parameters.AddWithValue("@High", dailyPrice.High);
                command.Parameters.AddWithValue("@Low", dailyPrice.Low);
                command.Parameters.AddWithValue("@Close", dailyPrice.Close);
                command.Parameters.AddWithValue("@Volume", dailyPrice.Volume);
                command.Parameters.AddWithValue("@PriceDate", dailyPrice.PriceDate);
                command.Parameters.AddWithValue("@AdjustedClose", dailyPrice.AdjustedClose);
                command.Parameters.AddWithValue("@AdjustedHigh", dailyPrice.AdjustedHigh);
                command.Parameters.AddWithValue("@AdjustedOpen", dailyPrice.AdjustedOpen);
                command.Parameters.AddWithValue("@AdjustedLow", dailyPrice.AdjustedLow);

                command.ExecuteNonQuery();

                //int result = (int)command.Parameters["RETURN_VALUE"].Value;
            }
        }

        public bool DoesDailyPriceExistByDateAndSymbol(DailyPrice dailyPrice)
        {
            bool exists = false;

            SqlConnection connection = CreateOpenConnection();

            using (SqlCommand command = new SqlCommand(DOES_DAILYPRICE_EXIST_BY_NAME_COMMAND, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("@PriceDate", dailyPrice.PriceDate);
                command.Parameters.AddWithValue("@SymbolId", dailyPrice.SymbolId);

                exists = Convert.ToBoolean(command.ExecuteScalar());
            }

            return exists;
        }

        /// <summary>
        /// Gets all daily prices for symbol from date until most recent. 
        /// Loads the daily prices into the Symbol item itself
        /// </summary>
        /// <param name="symbol"></param>
        /// <param name="dateFrom"></param>
        public void GetAllDailyPricesBySymbolIdFromDate(Symbol symbol, DateTime dateFrom)
        {
            SqlConnection connection = CreateOpenConnection();

            using (SqlCommand command = new SqlCommand(
                GET_ALL_DAILY_PRICES_BY_SYMBOLID_FROM_DATE, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("@FromDate", dateFrom);
                command.Parameters.AddWithValue("@SymbolId", symbol.SymbolId);

                using (SqlDataReader dataReader = command.ExecuteReader())
                {
                    symbol.Prices = new List<DailyPrice>();

                    if (dataReader.HasRows)
                    {
                        while (dataReader.Read())
                        {
                            //read prices
                            decimal high = Convert.ToDecimal(dataReader["High"]);
                            decimal low = Convert.ToDecimal(dataReader["Low"]);
                            decimal open = Convert.ToDecimal(dataReader["Open"]);
                            decimal close = Convert.ToDecimal(dataReader["Close"]);
                            decimal adjustedClose = Convert.ToDecimal(dataReader["AdjustedClose"]);
                            Int64 volume = Convert.ToInt32(dataReader["Volume"]);
                            DateTime priceDate = Convert.ToDateTime(dataReader["PriceDate"]);

                            //add daily price
                            symbol.Prices.Add(new DailyPrice()
                            {
                                High = high,
                                Low = low,
                                Open = open,
                                Close = close,
                                AdjustedClose = adjustedClose,
                                Volume = volume,
                                PriceDate = priceDate,
                                SymbolId = symbol.SymbolId
                            });
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Gets all daily prices for symbol from date until most recent. 
        /// Loads the daily prices into the Symbol item itself
        /// </summary>
        /// <param name="symbol"></param>
        /// <param name="dateFrom"></param>
        public List<DailyPrice> GetAllDailyPricesBySymbolNameFromDate(Symbol symbol, DateTime dateFrom)
        {
            List<DailyPrice> prices = null;
            SqlConnection connection = CreateOpenConnection();

            using (SqlCommand command = new SqlCommand(
                GET_ALL_DAILY_PRICES_BY_SYMBOLNAME_FROM_DATE, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("@FromDate", dateFrom);
                command.Parameters.AddWithValue("@SymbolName", symbol.Name);

                using (SqlDataReader dataReader = command.ExecuteReader())
                {
                    prices = new List<DailyPrice>();

                    if (dataReader.HasRows)
                    {
                        while (dataReader.Read())
                        {
                            //read prices
                            decimal high = Convert.ToDecimal(dataReader["High"]);
                            decimal low = Convert.ToDecimal(dataReader["Low"]);
                            decimal open = Convert.ToDecimal(dataReader["Open"]);
                            decimal close = Convert.ToDecimal(dataReader["Close"]);
                            decimal adjustedClose = Convert.ToDecimal(dataReader["AdjustedClose"]);
                            Int64 volume = Convert.ToInt32(dataReader["Volume"]);
                            DateTime priceDate = Convert.ToDateTime(dataReader["PriceDate"]);

                            //add daily price
                            prices.Add(new DailyPrice()
                            {
                                High = high,
                                Low = low,
                                Open = open,
                                Close = close,
                                AdjustedClose = adjustedClose,
                                Volume = volume,
                                PriceDate = priceDate,
                                SymbolId = symbol.SymbolId
                            });
                        }
                    }
                }
            }

            return prices;
        }

        public List<DailyPrice> GetAllPricesBySymbolId(int symbolId)
        {
            List<DailyPrice> prices = null;

            SqlConnection connection = CreateOpenConnection();

            using (SqlCommand command = new SqlCommand(
                GET_ALL_DAILY_PRICES_BY_SYMBOLID, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("@SymbolId", symbolId);

                using (SqlDataReader dataReader = command.ExecuteReader())
                {
                    prices = new List<DailyPrice>();

                    if (dataReader.HasRows)
                    {
                        while (dataReader.Read())
                        {
                            //read prices
                            decimal high = Convert.ToDecimal(dataReader["High"]);
                            decimal low = Convert.ToDecimal(dataReader["Low"]);
                            decimal open = Convert.ToDecimal(dataReader["Open"]);
                            decimal close = Convert.ToDecimal(dataReader["Close"]);
                            decimal adjustedClose = Convert.ToDecimal(dataReader["AdjustedClose"]);
                            Int64 volume = Convert.ToInt64(dataReader["Volume"]);
                            DateTime priceDate = Convert.ToDateTime(dataReader["PriceDate"]);

                            //add daily price
                            prices.Add(new DailyPrice()
                            {
                                High = high,
                                Low = low,
                                Open = open,
                                Close = close,
                                AdjustedClose = adjustedClose,
                                Volume = volume,
                                PriceDate = priceDate,
                                SymbolId = symbolId
                            });
                        }
                    }
                }
            }

            return prices;
        }

        /// <summary>
        /// Gets all daily prices for symbol. 
        /// Loads the daily prices into the Symbol item itself
        /// </summary>
        /// <param name="symbol"></param>
        /// <param name="dateFrom"></param>
        public void GetAllDailyPricesBySymbolId(Symbol symbol)
        {
            SqlConnection connection = CreateOpenConnection();

            using (SqlCommand command = new SqlCommand(
                GET_ALL_DAILY_PRICES_BY_SYMBOLID, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("@SymbolId", symbol.SymbolId);

                using (SqlDataReader dataReader = command.ExecuteReader())
                {
                    symbol.Prices = new List<DailyPrice>();

                    if (dataReader.HasRows)
                    {
                        while (dataReader.Read())
                        {
                            //read prices
                            decimal high = Convert.ToDecimal(dataReader["High"]);
                            decimal low = Convert.ToDecimal(dataReader["Low"]);
                            decimal open = Convert.ToDecimal(dataReader["Open"]);
                            decimal close = Convert.ToDecimal(dataReader["Close"]);
                            decimal adjustedClose = Convert.ToDecimal(dataReader["AdjustedClose"]);
                            Int64 volume = Convert.ToInt32(dataReader["Volume"]);
                            DateTime priceDate = Convert.ToDateTime(dataReader["PriceDate"]);

                            //add daily price
                            symbol.Prices.Add(new DailyPrice()
                            {
                                High = high,
                                Low = low,
                                Open = open,
                                Close = close,
                                AdjustedClose = adjustedClose,
                                Volume = volume,
                                PriceDate = priceDate,
                                SymbolId = symbol.SymbolId
                            });
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Gets all DailyPrices for the specified Symbol
        /// </summary>
        /// <param name="symbol"></param>
        /// <param name="dateFrom"></param>
        public static List<DailyPrice> GetAllDailyPricesBySymbol(string symbol)
        {
            List<DailyPrice> result = null;

            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(
                    GET_DAILY_PRICES_BY_SYMBOL, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    command.Parameters.AddWithValue("@Symbol", symbol);

                    using (SqlDataReader dataReader = command.ExecuteReader())
                    {
                        result = new List<DailyPrice>();

                        if (dataReader.HasRows)
                        {
                            while (dataReader.Read())
                            {
                                //read prices
                                decimal high = Convert.ToDecimal(dataReader["High"]);
                                decimal low = Convert.ToDecimal(dataReader["Low"]);
                                decimal open = Convert.ToDecimal(dataReader["Open"]);
                                decimal close = Convert.ToDecimal(dataReader["Close"]);
                                decimal adjustedClose = Convert.ToDecimal(dataReader["AdjustedClose"]);
                                Int64 volume = Convert.ToInt32(dataReader["Volume"]);
                                DateTime priceDate = Convert.ToDateTime(dataReader["PriceDate"]);

                                //add daily price
                                result.Add(new DailyPrice()
                                {
                                    High = high,
                                    Low = low,
                                    Open = open,
                                    Close = close,
                                    AdjustedClose = adjustedClose,
                                    Volume = volume,
                                    PriceDate = priceDate                                    
                                });
                            }
                        }
                    }
                }
            }

            return result;
        }
    }
}
